package com.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.excel.student.Student;
import com.frame.management.MainSystem;

public class Utils {
	public static final String PATH_STUDENT = "D:\\student.xls";
	public static Map<String,Student> studentMap = initStudentMap();

	public static Map<String, Student> initStudentMap() {
		Map<String, Student> map = new LinkedHashMap<>();
		HSSFWorkbook hssfWorkbook = null;
		try {

			File file = new File(PATH_STUDENT);
			//读取学生信息文件，如果文件不存在 读取初始化数据文件
			if(!file.exists()){
				System.out.println("学生信息文件不存在，读取初始化文件。");
				InputStream in = Utils.class.getClassLoader().getResourceAsStream("student-init.xls");
				POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
				hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
			}else{
				System.out.println("读取学生信息文件。");
				FileInputStream in = new FileInputStream(file);
				POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
				hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
			}

		} catch (Exception e) {
			System.out.println("读取学生信息文件出错.");
			e.printStackTrace();
		}

		// 得到第一个工作表
		HSSFSheet sheet1 = hssfWorkbook.getSheetAt(0);
		//遍历第一页的每一行
		Iterator<Row> rows = sheet1.rowIterator();
		int i = 0;
		while (rows.hasNext()) {
			//第一行是 学号 姓名 班级，不用解析 跳过
			if (i == 0) {
				i++;
				rows.next();
				continue;
			}
			HSSFRow row = (HSSFRow) rows.next();
			Student student = new Student();
			
			//得到每一行的 第 1 2 3 列
			HSSFCell cellId = row.getCell(0);
			HSSFCell cellName = row.getCell(1);
			HSSFCell cellCla = row.getCell(2);
			
			//得到一行的学生信息
			int id = (int) cellId.getNumericCellValue();
			String name = cellName.getStringCellValue();
			String cla = cellCla.getStringCellValue();

			student.setId(id);
			student.setName(name);
			student.setCla(cla);
			System.out.println("学生信息为："+student);
			
			map.put(String.valueOf(id), student);
			
		}
		
		return map;
	}
	
	
	//软件关闭的时候 把对应的学生信息写入到D盘的 student.xls文件
	public static void createStudentExcel() {
		File file = new File("d:\\student.xls");
		if(file.exists()){
			file.delete();
			System.out.println("删除原始的student文件.");
		}
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建Excel的工作sheet,对应到一个excel文档的tab
		HSSFSheet sheet = wb.createSheet("sheet1");
		
		//创建第一行 学号 姓名 班级
		HSSFRow row = sheet.createRow(0);
		HSSFCell cellId = row.createCell(0);
		HSSFCell cellName = row.createCell(1);
		HSSFCell cellCla = row.createCell(2);
		cellId.setCellValue("学号");
		cellName.setCellValue("姓名");
		cellCla.setCellValue("班级");
		
		//把 map里边的学生信息全部生成到 excel文件中
//		for (int j = 0; j < MainSystem.tableVales.length; j++) {
//			String[] values = MainSystem.tableVales[j];
//			Student student = new Student();
//			student.setId(Integer.parseInt(values[0]));
//			student.setName(values[1]);
//			student.setCla(values[2]);
//			row = sheet.createRow(i);
//			cellId = row.createCell(0);
//			cellName = row.createCell(1);
//			cellCla = row.createCell(2);
//			
//			cellId.setCellValue(student.getId());
//			cellName.setCellValue(student.getName());
//			cellCla.setCellValue(student.getCla());
//			i++;
//		}
		
		int i = 1;
		for (String key : studentMap.keySet()) {
			Student student = studentMap.get(key);
			row = sheet.createRow(i);
			cellId = row.createCell(0);
			cellName = row.createCell(1);
			cellCla = row.createCell(2);
			
			cellId.setCellValue(student.getId());
			cellName.setCellValue(student.getName());
			cellCla.setCellValue(student.getCla());
			i++;
		}

		try{
			FileOutputStream os = new FileOutputStream(file);  
		    wb.write(os); 
		    wb.close();
		    os.close(); 
		    System.out.println("更新学生信息到Excel文件。");
		}catch(IOException e){
			System.out.println("生成新的学生信息文件失败。");
			e.printStackTrace();
		}

	}

}
